Snowflakeのチュートリアル「Bulk Loading from Amazon S3 Using COPY」をやってみた #SnowflakeDB
これも20分くらいでできます
こんちわ。大阪オフィスの玉井です。
SnowflakeはS3にcsv等を置いておけば、そこからテーブルにデータをロード（コピー）することができます。今回はそれらに関する一連のチュートリアルをやってみました。
今回の作業環境
- Windows 10 Pro
- Google Chrome 77.0.3865.90
- Snowflake 2019/10/16時点の最新
ステップ0：準備
Snowflake側の準備
Snowflakeで、下記のクエリを実行しておきます（チュートリアルで使用するテーブル等の作成）。
create or replace database mydatabase; create or replace temporary table mycsvtable ( id integer, last_name string, first_name string, company string, email string, workphone string, cellphone string, streetaddress string, city string, postalcode string); create or replace temporary table myjsontable ( json_data variant); create or replace warehouse mywarehouse with warehouse_size='X-SMALL' auto_suspend = 120 auto_resume = true initially_suspended=true;
S3側の準備
このチュートリアル用にSnowflakeが用意しているS3バケットがあるので、特に準備は不要です。このバケットにはcsvファイルとjsonファイルが格納されています。
csvの一部
id|lastname|firstname|company|email|workphone|cellphone|streetaddress|city|postalcode 6|reed|moses|neque corporation|eget.lacus@facilisis.com|1-449-871-0780|1-454-964-5318|ap #225-4351 dolor ave|titagarh|62631
jsonの一部
[
{
"customer": {
"address": "509 Kings Hwy, Comptche, Missouri, 4848",
"phone": "+1 (999) 407-2274",
"email": "blankenship.patrick@orbin.ca",
"company": "ORBIN",
"name": {
"last": "Patrick",
"first": "Blankenship"
},
"_id": "5730864df388f1d653e37e6f"
}
},
]
ステップ1：ファイルフォーマットオブジェクトを作成する
ファイルフォーマットオブジェクトは、データをテーブルにコピーする際、元データ（ファイル）の形式を予め定義しておくものです。必須の作業ではないのですが、これをやっておくと、後でいちいち形式を指定しなくていいので、作業が楽になります。
cmtamai#COMPUTE_WH@MYDATABASE.PUBLIC>use database MYDATABASE;
+----------------------------------+
| status |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.156s
cmtamai#COMPUTE_WH@MYDATABASE.PUBLIC>create or replace file format mycsvformat_tutorial
type = 'CSV'
field_delimiter = '|'
skip_header = 1;
+--------------------------------------------------------+
| status |
|--------------------------------------------------------|
| File format MYCSVFORMAT_TUTORIAL successfully created. |
+--------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.208s
cmtamai#COMPUTE_WH@MYDATABASE.PUBLIC>create or replace file format myjsonformat
type = 'JSON'
strip_outer_array = true;
+------------------------------------------------+
| status |
|------------------------------------------------|
| File format MYJSONFORMAT successfully created. |
+------------------------------------------------+
指定するパラメータは読めばわかるもので、わかりやすいです（ファイル形式とか区切り文字とか）。
また、最初にどのDBで作業するかを指定するために、USE DATABASEを使用しています。
ステップ2：名前付きステージオブジェクトを作成する
簡単にいうと「外部ストレージをSnowflakeに定義する」という感じの作業です。外部ストレージ（S3）の場所をSnowflakeに教えてあげるという感じでしょうか。ちなみに、ここで先程作成したファイルフォーマットオブジェクトを指定します。「このS3に入っているファイルはこの形式です」という形ですかね。
今回はSnowflakeが公開しているS3バケットを使用します（パブリックじゃないバケットの場合は別途アクセスキーが必要です）。
cmtamai#COMPUTE_WH@MYDATABASE.PUBLIC>create or replace stage my_csv_stage
file_format = mycsvformat_tutorial
url = 's3://snowflake-docs';
+-----------------------------------------------+
| status |
|-----------------------------------------------|
| Stage area MY_CSV_STAGE successfully created. |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.191s
cmtamai#MYWAREHOUSE@MYDATABASE.PUBLIC>create or replace stage my_json_stage
file_format = myjsonformat
url = 's3://snowflake-docs';
+------------------------------------------------+
| status |
|------------------------------------------------|
| Stage area MY_JSON_STAGE successfully created. |
+------------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.362s
ステップ3：テーブルにデータをコピー
ファイル形式とS3を定義したところで、テーブルにS3（に配置している）のデータをコピーします。
csv
単一のファイルを指定
cmtamai#MYWAREHOUSE@MYDATABASE.PUBLIC>copy into mycsvtable
from @my_csv_stage/tutorials/dataloading/contacts1.csv
on_error = 'skip_file';
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts1.csv | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL |
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 3.575s
ワイルドカードで複数のファイルを指定
cmtamai#MYWAREHOUSE@MYDATABASE.PUBLIC>copy into mycsvtable
from @my_csv_stage/tutorials/dataloading/
pattern='.*contacts[1-5].csv'
on_error = 'skip_file';
+---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
| file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts2.csv | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL |
| s3://snowflake-docs/tutorials/dataloading/contacts4.csv | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL |
| s3://snowflake-docs/tutorials/dataloading/contacts3.csv | LOAD_FAILED | 5 | 0 | 1 | 2 | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | 3 | 1 | "MYCSVTABLE"[11] |
| s3://snowflake-docs/tutorials/dataloading/contacts5.csv | LOADED | 6 | 6 | 1 | 0 | NULL | NULL | NULL | NULL |
+---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
4 Row(s) produced. Time Elapsed: 2.898s
上記のワイルドカード版を実行した時、処理結果としては下記のようになりました。
- contacts1.csvはさっきテーブルに入れたので処理はスルーされた
- contacts2.csv、contacts4.csv、contacts5.csvは正常に処理が完了した
- contacts3.csvは2件のエラーがあったため処理はスルーされた
ちなみに、今回のクエリではon_error = 'skip_file'としているため、エラーが発生しても、そこだけスキップして、後続の処理は実行するようになっています。デフォルトではエラーが発生した時点で処理はストップします。
json
cmtamai#MYWAREHOUSE@MYDATABASE.PUBLIC>copy into myjsontable
from @my_json_stage/tutorials/dataloading/contacts.json
on_error = 'skip_file';
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts.json | LOADED | 3 | 3 | 1 | 0 | NULL | NULL | NULL | NULL |
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 2.731s
JSONファイルも正常にコピーできました。
ステップ4：エラーの原因を探る
ステップ3では、CSVファイルのコピー時にエラーが発生しました。このステップでエラーの原因をチェックします。
エラー情報をテーブルに格納する
まず、エラー自体の情報を確認する必要があります。Snowflakeの管理画面にログインし、画面上部メニューのHistoryを選びます。そして、先程エラーがあったクエリを見つけます。
該当クエリのQuery IDを選択してDetailを確認します。
Detail画面でQuery IDをコピっておき、下記のクエリを実行します。
create or replace table save_copy_errors as select * from table(validate(mycsvtable, job_id=>'<query_id>'));
cmtamai#MYWAREHOUSE@MYDATABASE.PUBLIC>create or replace table save_copy_errors as select * from table(validate(mycsvtable, job_id=>'018f9909-006e-4746-0000-12950003147e')); +----------------------------------------------+ | status | |----------------------------------------------| | Table SAVE_COPY_ERRORS successfully created. | +----------------------------------------------+ 1 Row(s) produced. Time Elapsed: 3.111s
格納したエラー情報を確認します。
cmtamai#MYWAREHOUSE@MYDATABASE.PUBLIC>select * from save_copy_errors; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | ERROR | FILE | LINE | CHARACTER | BYTE_OFFSET | CATEGORY | CODE | SQL_STATE | COLUMN_NAME | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------| | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | tutorials/dataloading/contacts3.csv | 3 | 1 | 234 | parsing | 100080 | 22000 | "MYCSVTABLE"[11] | 1 | 2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 | | | | | | | | | | | | | | | Field delimiter '|' found while expecting record delimiter '\n' | tutorials/dataloading/contacts3.csv | 5 | 125 | 625 | parsing | 100016 | 22000 | "MYCSVTABLE"["POSTALCODE":10] | 4 | 5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188 | | | | | | | | | | | | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ 2 Row(s) produced. Time Elapsed: 0.595s
エラーの詳細を確認
上記でわかったのは、contacts3.csvで2件のエラーがあったということです。
- 1行目で、ハイフンが区切り文字であるパイプに変換されてしまい、列が1つ増えてしまってエラー
- 5行目で、ハイフンの後にパイプが追加されているため、レコードが破損扱いになってエラー
修正について
今回はデータの中身自体に問題があるため、手動なりなんなりでデータ自体を修正するしかありません。
ステップ5：データの確認
今回入れたデータは正しく入っているのでしょうか。それを確認します。普通のSELECT文が使えます。
cmtamai#MYWAREHOUSE@MYDATABASE.PUBLIC>select * from mycsvtable; +----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+--------------------------------+------------------+------------+ | ID | LAST_NAME | FIRST_NAME | COMPANY | EMAIL | WORKPHONE | CELLPHONE | STREETADDRESS | CITY | POSTALCODE | |----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+--------------------------------+------------------+------------| | 6 | Reed | Moses | Neque Corporation | eget.lacus@facilisis.com | 1-449-871-0780 | 1-454-964-5318 | Ap #225-4351 Dolor Ave | Titagarh | 62631 | | 7 | Audrey | Franks | Arcu Eu Limited | eu.dui@aceleifendvitae.org | 1-527-945-8935 | 1-263-127-1173 | Ap #786-9241 Mauris Road | Bergen | 81958 | | 8 | Jakeem | Erickson | A Ltd | Pellentesque.habitant@liberoProinmi.ca | 1-381-591-9386 | 1-379-391-9490 | 319-1703 Dis Rd. | Pangnirtung | 62399 | | 9 | Xaviera | Brennan | Bibendum Ullamcorper Limited | facilisi.Sed.neque@dictum.edu | 1-260-757-1919 | 1-211-651-0925 | P.O. Box 146, 8385 Vel Road | Béziers | 13082 | | 10 | Francis | Ortega | Vitae Velit Egestas Associates | egestas.rhoncus.Proin@faucibus.com | 1-257-584-6487 | 1-211-870-2111 | 733-7191 Neque Rd. | Chatillon | 33081 | | 1 | Imani | Davidson | At Ltd | nec@sem.net | 1-243-889-8106 | 1-730-771-0412 | 369-6531 Molestie St. | Russell | 74398 | | 2 | Kelsie | Abbott | Neque Sed Institute | lacus@pede.net | 1-467-506-9933 | 1-441-508-7753 | P.O. Box 548, 1930 Pede. Road | Campbellton | 27022 | | 3 | Hilel | Durham | Pede Incorporated | eu@Craspellentesque.net | 1-752-108-4210 | 1-391-449-8733 | Ap #180-2360 Nisl. Street | Etalle | 84025 | | 4 | Graiden | Molina | Sapien Institute | sit@fermentum.net | 1-130-156-6666 | 1-269-605-7776 | 8890 A, Rd. | Dundee | 70504 | | 5 | Karyn | Howard | Pede Ac Industries | sed.hendrerit@ornaretortorat.edu | 1-109-166-5492 | 1-506-782-5089 | P.O. Box 902, 5398 Et, St. | Saint-Hilarion | 26232 | | 16 | Aretha | Sykes | Lobortis Tellus Justo Foundation | eget@Naminterdumenim.net | 1-670-849-1866 | 1-283-783-3710 | Ap #979-2481 Dui. Av. | Thurso | 66851 | | 17 | Akeem | Casey | Pharetra Quisque Ac Institute | dictum.eu@magna.edu | 1-277-657-0361 | 1-623-630-8848 | Ap #363-6074 Ullamcorper, Rd. | Idar-Oberstei | 30848 | | 18 | Keelie | Mendez | Purus In Foundation | Nulla.eu.neque@Aeneanegetmetus.co.uk | 1-330-370-8231 | 1-301-568-0413 | 3511 Tincidunt Street | Lanklaar | 73942 | | 19 | Lane | Bishop | Libero At PC | non@dapibusligula.ca | 1-340-862-4623 | 1-513-820-9039 | 7459 Pede. Street | Linkebeek | 89252 | | 20 | Michelle | Dickson | Ut Limited | Duis.dignissim.tempor@cursuset.org | 1-202-490-0151 | 1-129-553-7398 | 6752 Eros. St. | Stornaway | 61290 | | 20 | Michelle | Dickson | Ut Limited | Duis.dignissim.tempor@cursuset.org | 1-202-490-0151 | 1-129-553-7398 | 6752 Eros. St. | Stornaway | 61290 | | 21 | Lance | Harper | Rutrum Lorem Limited | Sed.neque@risus.com | 1-685-778-6726 | 1-494-188-6168 | 663-7682 Et St. | Gisborne | 73449 | | 22 | Keely | Pace | Eleifend Limited | ante.bibendum.ullamcorper@necenim.edu | 1-312-381-5244 | 1-432-225-9226 | P.O. Box 506, 5233 Aliquam Av. | Woodlands County | 61213 | | 23 | Sage | Leblanc | Egestas A Consulting | dapibus@elementum.org | 1-630-981-0327 | 1-301-287-0495 | 4463 Lorem Road | Woodlands County | 33951 | | 24 | Marny | Holt | Urna Nec Luctus Associates | ornare@vitaeorci.ca | 1-522-364-3947 | 1-460-971-8360 | P.O. Box 311, 4839 Nulla Av. | Port Coquitlam | 36733 | | 25 | Holly | Park | Mauris PC | Vestibulum.ante@Maecenasliberoest.org | 1-370-197-9316 | 1-411-413-4602 | P.O. Box 732, 8967 Eu Avenue | Provost | 45507 | +----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+--------------------------------+------------------+------------+ 21 Row(s) produced. Time Elapsed: 1.061s
cmtamai#MYWAREHOUSE@MYDATABASE.PUBLIC>select * from myjsontable;
+-----------------------------------------------------------------+
| JSON_DATA |
|-----------------------------------------------------------------|
| { |
| "customer": { |
| "_id": "5730864df388f1d653e37e6f", |
| "address": "509 Kings Hwy, Comptche, Missouri, 4848", |
| "company": "ORBIN", |
| "email": "blankenship.patrick@orbin.ca", |
| "name": { |
| "first": "Blankenship", |
| "last": "Patrick" |
| }, |
| "phone": "+1 (999) 407-2274" |
| } |
| } |
| { |
| "customer": { |
| "_id": "5730864d4d8523c8baa8baf6", |
| "address": "290 Lefferts Avenue, Malott, Delaware, 1575", |
| "company": "SNIPS", |
| "email": "anna.glass@snips.name", |
| "name": { |
| "first": "Anna", |
| "last": "Glass" |
| }, |
| "phone": "+1 (958) 411-2876" |
| } |
| } |
| { |
| "customer": { |
| "_id": "5730864e375e08523150fc04", |
| "address": "756 Randolph Street, Omar, Rhode Island, 3310", |
| "company": "ESCHOIR", |
| "email": "sparks.ramos@eschoir.co.uk", |
| "name": { |
| "first": "Sparks", |
| "last": "Ramos" |
| }, |
| "phone": "+1 (962) 436-2519" |
| } |
| } |
+-----------------------------------------------------------------+
3 Row(s) produced. Time Elapsed: 1.281s
JSONはそのまま格納されています。
ステップ6：後片付け
今回作成したテーブル等が不要な場合は、DROP文で削除することができます。
drop database if exists mydatabase; drop warehouse if exists mywarehouse;
おわりに
入れるファイルの形式を事前にしっかり把握しておくことが大事ですね。
![[アップデート]S3 Express One Zone に S3 ライフサイクルルールを設定できるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-066beb776f0c57ce64255fadcc072f60/82b2f6687ab5774cd73f9176dcac7855/amazon-s3)
